﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
namespace INTEX.DataBase.Tools
{
    public partial class INTEXDBC
    {
        public void MapICC_ABCOST(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveICC_ABCOST(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet Result = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            Result = QueryICC_ABCOST(dwData);
            if (Result.Tables.Count > 0)
            {
                tbLocal = Result.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }

            MapICC_ABCOST(ref dwLocal, dwData);

            if (!flgExist)
            {
                InsertICC_ABCOST(dwData, 1);
            }
            UpdateICC_ABCOST(dwLocal);

        }
        public void InsertICC_ABCOST(DataRow dwData, int option = 1)
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1://20140501
                    or1.CommandText = "INSERT INTO ICC_ABCOST (ROWNID,MANDANT,MONTH,LO,FART,FVAR,MART,MVAR,AB36)"
                        + "    SELECT SEQ_ICC_ITX.NEXTVAL AS ROWNID,   Y.GABMANDANT AS MANDANT,Y.XMONTH AS MONTH, GABLO AS LO,ABPART AS FART, ABPVAR AS FVAR,GABART AS MART,GABVAR AS MVAR, Y.AB36 AS AB36  "
                        + "  FROM  ( SELECT GABMANDANT ,GABLO,RTRIM(GABBUCHDAT) AS XMONTH,ABPART, ABPVAR, GABART,GABVAR,SUM(X.GABKG) AS AB36   "
                        + " FROM "
                        + " (  SELECT  GABMANDANT,GABLO,GABBUCHDAT,ABPAUFKRS,ABPAUFLFD,ABPAUFPOS, ABPART,ABPVAR,ABPFARBE,GABART,GABVAR,GABFARBE,SUM(GABKG) AS GABKG   FROM ICC_MGABEW W  LEFT JOIN ABPOS P   ON  W.GABMANDANT=ABPMANDANT AND W.GABAUFKRS=ABPAUFKRS   AND W.GABAUFLFD=ABPAUFLFD AND  W.GABAUFPOS=ABPAUFPOS   "
                        + "  WHERE GABAUFTYP='V' AND  GABAUFKRS NOT LIKE 'D%' AND GABMANDANT=:MANDANT AND RTRIM(GABBUCHDAT)=:MONTH  AND GABBS IN('38','58')     "
                        + "     GROUP BY GABMANDANT,GABLO,GABBUCHDAT,ABPAUFKRS,ABPAUFLFD,ABPAUFPOS,   ABPART,ABPVAR,ABPFARBE,GABART,GABVAR,GABFARBE  ) X       "
                        + " GROUP BY GABMANDANT,GABLO,RTRIM(GABBUCHDAT),ABPART,ABPVAR,GABART,GABVAR) Y     "
                        + "  WHERE NOT EXISTS  "
                        + " (SELECT 1 FROM ICC_ABCOST A WHERE A.MANDANT=Y.GABMANDANT AND A.MONTH=Y.XMONTH AND A.LO=Y.GABLO AND A.FART=Y.ABPART AND A.FVAR=ABPVAR AND  A.MART=Y.GABART AND A.MVAR=GABVAR) ";
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    or1.Parameters.Add("MONTH", OracleType.NVarChar).Value = dwData["MONTH"].ToString();
                    break;
                default:
                    break;
            }

            Connect2Proxy(or1);
        }
        public void DeleteICC_ABCOST(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM ICC_ABCOST X WHERE X.MANDANT=:MANDANT";
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    break;
                case 2:
                    or1.CommandText = "DELETE FROM ICC_ABCOST X WHERE X.MANDANT=:MANDANT AND X.MONTH=:MONTH";
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    or1.Parameters.Add("MONTH", OracleType.NVarChar).Value = dwData["MONTH"].ToString();
                    break;
                default:
                    break;
            } 
            Connect2Proxy(or1);
        }
        public DataSet QueryICC_ABCOST(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "SELECT * FROM ICC_ABCOST WHERE MANDANT=:MANDANT  AND MONTH=:MONTH"; ;
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    or1.Parameters.Add("MONTH", OracleType.NVarChar).Value = dwData["MONTH"].ToString();
                    break;
                case 2:
                    or1.CommandText = "SELECT * FROM ICC_ABCOST WHERE MANDANT=:MANDANT  AND MONTH=:MONTH AND LO=:LO AND FART=:FART AND MART=:MART AND MVAR=:MVAR AND MFARBE=:MFARBE"; ;
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    or1.Parameters.Add("MONTH", OracleType.NVarChar).Value = dwData["MONTH"].ToString();
                    or1.Parameters.Add("LO", OracleType.NVarChar).Value = dwData["LO"].ToString();
                    or1.Parameters.Add("FART", OracleType.NVarChar).Value = dwData["FART"].ToString();
                    or1.Parameters.Add("MART", OracleType.NVarChar).Value = dwData["MART"].ToString();
                    or1.Parameters.Add("MVAR", OracleType.NVarChar).Value = dwData["MVAR"].ToString();
                    or1.Parameters.Add("MFARBE", OracleType.NVarChar).Value = dwData["MFARBE"].ToString();
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "ICC_ABCOST";
            Return = Result;
            return Return;
        }
        public void UpdateICC_ABCOST(DataRow dwData, int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command====
                    or1.CommandText = "UPDATE ICC_ABCOST SET "
        + "COMPANY=:COMPANY,"
        + "USERID=:USERID,"
        + "PROCESS=:PROCESS,"
        + "MENUITEM=:MENUITEM,"
        + "MENUID=:MENUID,"
        + "FATHER=:FATHER,"
        + "HEADER=:HEADER,"
        + "TAG=:TAG,"
        + "ICON=:ICON,"
        + "LARGEICON=:LARGEICON,"
        + "JOB=:JOB,"
        + "TIMER=:TIMER,"
        + "SCHEDULETYPE=:SCHEDULETYPE,"
        + "FREQUENCY=:FREQUENCY,"
        + "TIME=:TIME "
                        + " WHERE  "
                        + "  MANDANT=:MANDANT AND ROWNID=:ROWNID";
                    #endregion

                    #region====Parameters====
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    or1.Parameters.Add("ROWNID", OracleType.NVarChar).Value = dwData["ROWNID"].ToString();
                    or1.Parameters.Add("COMPANY", OracleType.NVarChar).Value = dwData["COMPANY"].ToString();
                    or1.Parameters.Add("USERID", OracleType.NVarChar).Value = dwData["USERID"].ToString();
                    or1.Parameters.Add("PROCESS", OracleType.NVarChar).Value = dwData["PROCESS"].ToString();
                    or1.Parameters.Add("MENUITEM", OracleType.NVarChar).Value = dwData["MENUITEM"].ToString();
                    or1.Parameters.Add("MENUID", OracleType.NVarChar).Value = dwData["MENUID"].ToString();
                    or1.Parameters.Add("FATHER", OracleType.NVarChar).Value = dwData["FATHER"].ToString();
                    or1.Parameters.Add("HEADER", OracleType.NVarChar).Value = dwData["HEADER"].ToString();
                    or1.Parameters.Add("TAG", OracleType.NVarChar).Value = dwData["TAG"].ToString();
                    or1.Parameters.Add("ICON", OracleType.NVarChar).Value = dwData["ICON"].ToString();
                    or1.Parameters.Add("LARGEICON", OracleType.NVarChar).Value = dwData["LARGEICON"].ToString();
                    or1.Parameters.Add("JOB", OracleType.NVarChar).Value = dwData["JOB"].ToString();
                    or1.Parameters.Add("TIMER", OracleType.NVarChar).Value = dwData["TIMER"].ToString();
                    or1.Parameters.Add("SCHEDULETYPE", OracleType.NVarChar).Value = dwData["SCHEDULETYPE"].ToString();
                    or1.Parameters.Add("FREQUENCY", OracleType.NVarChar).Value = dwData["FREQUENCY"].ToString();
                    or1.Parameters.Add("TIME", OracleType.NVarChar).Value = dwData["TIME"].ToString();
                    #endregion
                    break;
                case 2:
                    or1.CommandText = "   UPDATE ICC_ABCOST A SET TAB36=  (SELECT  X.TAB36 FROM(SELECT MANDANT,MONTH,LO,MART,MVAR,SUM(AB36) AS TAB36  "
                        +"  FROM ICC_ABCOST GROUP BY MANDANT,MONTH,LO,MART,MVAR) X "
                        +" WHERE A.MANDANT=X.MANDANT AND A.MONTH=X.MONTH  AND A.LO=X.LO AND A.MART=X.MART AND A.MVAR=X.MVAR  )"
                        + "  WHERE A.MANDANT=:MANDANT AND A.MONTH=:MONTH   AND EXISTS (SELECT 1 FROM  (SELECT MANDANT,MONTH,LO,MART,MVAR,SUM(AB36) AS TAB36  "
                        +"  FROM ICC_ABCOST GROUP BY MANDANT,MONTH,LO,MART,MVAR) X"
                        +"  WHERE A.MANDANT=X.MANDANT AND A.MONTH=X.MONTH  AND A.LO=X.LO AND A.MART=X.MART AND A.MVAR=X.MVAR)";
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    or1.Parameters.Add("MONTH", OracleType.NVarChar).Value = dwData["MONTH"].ToString();
                    break;
                case 3:
                    or1.CommandText = "UPDATE ICC_ABCOST  A SET PTAB36=AB36/TAB36 WHERE TAB36>0 AND A.MANDANT=:MANDANT AND A.MONTH=:MONTH";
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    or1.Parameters.Add("MONTH", OracleType.NVarChar).Value = dwData["MONTH"].ToString();
                    break;
                case 4:
                    or1.CommandText = "UPDATE ICC_ABCOST T SET AB13=(SELECT YAB13 FROM ICC_YAB13 Y "
                        + " WHERE T.MANDANT=:MANDANT AND T.MONTH=:MONTH AND"
                        + " Y.GABMANDANT=T.MANDANT AND Y.GABLO=T.LO AND RTRIM(Y.GABBUCHDAT)=T.MONTH AND Y.GABART=T.FART AND Y.GABVAR=T.FVAR)"
                        + " WHERE EXISTS (SELECT 1 FROM ICC_YAB13 Y WHERE T.MANDANT=:MANDANT AND T.MONTH=:MONTH AND"
                        + " Y.GABMANDANT=T.MANDANT AND Y.GABLO=T.LO AND RTRIM(Y.GABBUCHDAT)=T.MONTH"
                        + " AND Y.GABART=T.FART AND Y.GABVAR=T.FVAR)";
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    or1.Parameters.Add("MONTH", OracleType.NVarChar).Value = dwData["MONTH"].ToString();
                    break;
                case 5://20140501
                    or1.CommandText = "UPDATE ICC_ABCOST A SET FARTDES="
                        +" (SELECT DISTINCT GSAGARNNR FROM GSDAT WHERE   GSMANDANT=MANDANT AND FART=GSART AND FVAR=GSVAR)"
                        + "  WHERE A.MANDANT=:MANDANT AND A.MONTH=:MONTH AND EXISTS"
                        + "  (SELECT 1 FROM GSDAT WHERE   GSMANDANT=MANDANT AND FART=GSART AND FVAR=GSVAR) ";
                    or1.Parameters.Add("MANDANT", OracleType.NVarChar).Value = dwData["MANDANT"].ToString();
                    or1.Parameters.Add("MONTH", OracleType.NVarChar).Value = dwData["MONTH"].ToString();
                    break;
            }
            Connect2Proxy(or1);
        }
    }
}
